今天用同一張表繼續介紹 聚合,having,groupby
今天用到的 table
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
salesperson VARCHAR(255),
region VARCHAR(255),
amount NUMERIC
);
INSERT INTO sales (salesperson, region, amount) VALUES
('Alice', 'North', 500),
('Bob', 'North', 600),
('Alice', 'South', 700),
('Charlie', 'East', 400),
('Alice', 'North', 200),
('Bob', 'South', 300);
聚合函式簡單介紹,更多可以參考
sum: 計算欄位內容的總和
count: 計算有幾筆資料(record, row)
min, max:找出資料row 裡面的最大值或最小值(數值、日期、甚至文字也都可以)
---查詢銷售記錄的數量
SELECT COUNT(*) AS total_records FROM sales;
---計算銷售額不為 NULL 的記錄
SELECT COUNT(amount) AS total_sales_records FROM sales;
SELECT MIN(amount) AS min_sales FROM sales;
SELECT
MIN(amount) AS min_sales,
MAX(amount) AS max_sales
FROM sales;
明天繼續介紹 groupby 跟 having 關鍵字,這兩個跟聚合函數有關!